from py2neo import Graph,Node,Relationship,NodeMatcher,RelationshipMatcher
from db_utils.mysql_utils import MysqlInstance
from settings import *
from db_utils.dict_utils import split_values_from_keys
from common.sql_dict import *
from tqdm import tqdm
import csv

class createBHPData(object):
    def __init__(self):
        # 建立连接

        self.mysql_obj = MysqlInstance(default_connect)


        #实体映射表
        self.entity_table_dict = {
            "crm_enterprise":["crm_enterprise_id","enterprise_name","enterprise_address"],
            'crm_project':["crm_project_id","project_fullname","project_address","project_province","project_city","project_region","project_description"],
            "crm_project_contactor":["crm_project_contactor_id",'projectcontractor_name','projectcontractor_tel'],
            "crm_tender_announcement":["crm_tender_announcement_id",'project_section_name',"project_section_bidder_qualification_level"]
        }


        self.entity_to_label_dict = {
            "crm_enterprise": entity_label_company,
            'crm_project': entity_label_project,
            "crm_project_contactor":entity_label_company_project_major,
            "crm_tender_announcement": entity_label_project_section
        }

        self.csv_head = {
            "crm_enterprise":['crm_enterprise_id:ID',"公司名称","公司地址",'name',":LABEL"],
            "crm_project":['crm_project_id:ID','项目名称',"项目地址","项目所属省","项目所属市","项目所属区","项目简介","name",":LABEL"],
            "crm_project_contactor":["crm_project_contactor_id:ID","姓名","电话","name",":LABEL"],
            "crm_tender_announcement":["crm_tender_announcement_id:ID","标段名称","标段要求建筑资质","name",":LABEL"]
        }






    def convert_entitys(self):
        for key,value in self.entity_table_dict.items():
            with open("csv_data/"+key+".csv",'w',encoding='utf-8',newline='') as fwrite:
                csv_write = csv.writer(fwrite)
                csv_write.writerow(self.csv_head[key])

                if key == "crm_enterprise":
                    sql = 'select * from crm_enterprise ce  where ce.crm_enterprise_id  in ( select DISTINCT(PK_crm_enterprise_id) from crm_enterprise_bidding ceb)'
                    infos = self.mysql_obj.select_excute_sql(sql)
                else:
                    infos = self.mysql_obj.search_info_from_mysql(key, {},value)
                for info in tqdm(infos):
                    info = split_values_from_keys(info,eval("convert_zh_"+ key))

                    for sub_key,value in list(info.items()):
                        if not value:
                            info[sub_key] = ""
                        if '名称' in sub_key:
                            info['name'] = value
                        elif "姓名" in sub_key:
                            info['name'] = value
                    insert_row = []
                    insert_row.append(key + str(info[key+"_id"]))
                    for sub_key in self.csv_head[key][1:-2]:
                        insert_row.append(info[sub_key])
                    insert_row.append(info['name'])
                    insert_row.append(self.entity_to_label_dict[key])
                    csv_write.writerow(insert_row)

    def insert_relations_toubiao(self):
        '''
        插入公司和项目的投标关系
        :return:
        '''
        #sql = 'select crm_enterprise_bidding_id ,PK_crm_tender_announcement_id ,PK_crm_enterprise_id ,is_win_bidding ,win_bidding_time from crm_enterprise_bidding ceb join crm_enterprise ce  on ce.crm_enterprise_id = ceb.PK_crm_enterprise_id join crm_tender_announcement cta on cta.crm_tender_announcement_id = ceb.PK_crm_tender_announcement_id'
        #results = self.mysql_obj.cur.execute_sql(sql)
        relation_set = set()
        sql = "select * from crm_enterprise_bidding where PK_crm_enterprise_id in ( select crm_enterprise_id from crm_enterprise)"
        infos = self.mysql_obj.select_excute_sql(sql)
        #infos = self.mysql_obj.search_info_from_mysql("crm_enterprise_bidding", {})
        with open("csv_data/relation_toubiao.csv",'w',encoding='utf-8',newline="") as fwrite:
            csv_write = csv.writer(fwrite)
            csv_write.writerow([':START_ID',"是否中标","中标时间",":END_ID",":TYPE"])

            for info in tqdm(infos):
                if not info['PK_crm_enterprise_id']:
                    continue
                insert_list = []
                insert_list.append("crm_enterprise" + str(info['PK_crm_enterprise_id']))

                if info['is_win_bidding']:
                    insert_list.append("1")
                else:
                    insert_list.append("0")

                if info['win_bidding_time']:
                    insert_list.append(info['win_bidding_time'])
                else:
                    insert_list.append("")

                insert_list.append("crm_tender_announcement" + str(info['PK_crm_tender_announcement_id']))
                insert_list.append(relation_bidding)
                if "_".join(insert_list) not in relation_set:
                    csv_write.writerow(insert_list)
                    relation_set.add("_".join(insert_list))


    def insert_realtions_jiuzhi(self):
        '''
        插入公司和项目经理的就职关系
        :return:
        '''
        sql = "select * from crm_enterprise_bidding where PK_crm_enterprise_id in ( select crm_enterprise_id from crm_enterprise)"
        infos = self.mysql_obj.select_excute_sql(sql)
        relation_set_1 = set()
        relation_set_2 = set()
        with open("csv_data/relation_work_in.csv",'w',encoding='utf-8',newline="") as fwrite_work_in:
            with open("csv_data/relation_project_major.csv",'w',encoding='utf-8',newline="") as fwrite_project_major:
                csv_write_work_in = csv.writer(fwrite_work_in)
                csv_write_work_in.writerow([':START_ID', ":END_ID", ":TYPE"])

                csv_write_project_major = csv.writer(fwrite_project_major)
                csv_write_project_major.writerow([':START_ID', ":END_ID", ":TYPE"])
                for info in tqdm(infos):
                    if info['PK_crm_enterprise_id'] and info['PK_crm_project_contactor_id']:
                        insert_list = []
                        insert_list.append("crm_project_contactor"+str(info['PK_crm_project_contactor_id']))
                        insert_list.append("crm_enterprise" + str(info['PK_crm_enterprise_id']))
                        insert_list.append(relation_work_in)
                        if "_".join(insert_list) not in relation_set_1:
                            csv_write_work_in.writerow(insert_list)
                            relation_set_1.add("_".join(insert_list))


                    if info['PK_crm_enterprise_id'] and info['PK_crm_project_contactor_id'] and info[
                        'PK_crm_project_contactor_id'] and info['is_win_bidding']:

                        insert_list = []
                        insert_list.append("crm_project_contactor" + str(info['PK_crm_project_contactor_id']))
                        insert_list.append("crm_tender_announcement" + str(info['PK_crm_tender_announcement_id']))
                        insert_list.append(relation_win_bidding_company_project_major)

                        if "_".join(insert_list) not in relation_set_2:
                            csv_write_project_major.writerow(insert_list)
                            relation_set_2.add("_".join(insert_list))


    def insert_relation_biaoduan(self):
        infos = self.mysql_obj.search_info_from_mysql("crm_tender_announcement", {})
        relation_set_1 = set()
        with open("csv_data/relation_biaoduan.csv",'w',encoding='utf-8',newline="") as fwrite:
            csv_write = csv.writer(fwrite)
            csv_write.writerow([':START_ID', ":END_ID", ":TYPE"])
            for info in tqdm(infos):
                insert_list = []
                insert_list.append("crm_project" + str(info['PK_crm_project_id']))
                insert_list.append("crm_tender_announcement" + str(info['crm_tender_announcement_id']))
                insert_list.append(relation_section)
                if "_".join(insert_list) not in relation_set_1:
                    relation_set_1.add("_".join(insert_list))
                    csv_write.writerow(insert_list)



if __name__ == '__main__':
    a = createBHPData()
    a.convert_entitys()

    a.insert_relation_biaoduan()

    a.insert_realtions_jiuzhi()
    a.insert_relations_toubiao()
